WHERE Query

The WHERE command is used to select records based on a condition. It returns only those records that satisfy the conditions specified in the WHERE command, effectively filtering the records.

For Example:

In the employees table, Let's select only those records whose salary is 9000.

select * from employees where salary = 9000;

Following where, salary is the column name which has to be equal to the value 9000. select from where has to be in this order only.

Result:

Note:

In SQL, anything inside single quotes (' ') is treated as a string. For example, '123' is a string, and 'name' is also a string.

Anything inside double quotes (' ') is treated as a column name. If you put any words inside double quotes, SQL will consider them as a column name and search for it."

As you see In the above example, it says column "9000" does not exist. SQL considers "9000" as a column name.

Ecto query for where

where/3

Expression

In Ecto.Query where/3 is used to fetch records based on a particular value.

For Example:

HR.Employee
|> where([c], c.salary == 9000)
|> HR.Repo.all()

Result:

IEx(26)> HR.Employee |> where([c], c.salary == 9000) |> HR.Repo.all()

[
 %HR.Employee{
   __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
   employee_id: 103,
   first_name: "Alexander",
   last_name: "Hunold",
   email: "alexander.hunold@sqltutorial.org",
   phone_number: "590.423.4567",
   hire_date: ~D[1990-01-03],
   salary: Decimal.new("9000.00"),
   manager_id: 102,
   job_id: 9,
   department_id: 6
 },
 %HR.Employee{
   __meta__: #Ecto.Schema.Metadata<:loaded, "employees">,
   employee_id: 109,
   first_name: "Daniel",
   last_name: "Faviet",
   email: "daniel.faviet@sqltutorial.org",
   phone_number: "515.124.4169",
   hire_date: ~D[1994-08-16],
   salary: Decimal.new("9000.00"),
   manager_id: 108,
   job_id: 6,
   department_id: 10
 }
]

In this example, where([c], c.salary == 9000) uses [c] as the reference variable to refer to HR.Employee. This query will fetch the HR.Employee struct where the salary is equal to 9000. Refer to [Aliases in Ecto](/blog/Aliases in Ecto) to learn about aliases/reference variables.

Keywords

HR.Repo.all(from c in HR.Employee, where: c.salary == 9000)

In this example, where is a key, and c.salary == 9000 is its value, which specifies that only records where the salary is equal to 9000 should be returned.

Back to Homepage